<?php
namespace App\Core\Mysql;

use App\Core\Container;
use Exception;

class Query
{
    /**
     * @var array
     */
    private array $where = [];

    /**
     * @var array
     */
    private array $updateWhere = [];

    /**
     * @var array
     */
    private array $deleteWhere = [];

    /**
     * @var array
     */
    private array $params = [];

    /**
     * @var string[]|string
     */
    private $select = ['*'];

    /**
     * @var string
     */
    private string $orderBy = '';

    /**
     * @var string
     */
    private string $groupBy = '';

    /**
     * @var int
     */
    private int $limit = 0;

    /**
     * @var int
     */
    private int $offset = 0;

    /**
     * @var array
     */
    private array $insert = [];

    /**
     * @var array
     */
    private array $update = [];

    /**
     * @var string
     */
    public string $table = '';

    /**
     * @var string
     */
    public string $db = 'db';

    /**
     * @return $this
     */
    public static function find()
    {
        $key = static::class;
        $obj = Container::get($key);
        if($obj === null){
            $obj = new static();
            Container::set($key,$obj);
        }
        $obj->init(); //每次重新获取对象时，重置对象
        return $obj;
    }

    /**
     * 初始化 Query数据
     */
    private function init()
    {
        $this->where = [];
        $this->deleteWhere = [];
        $this->updateWhere = [];
        $this->update = [];
        $this->insert = [];
        $this->select = ['*'];
        $this->limit = 0;
        $this->offset = 0;
        $this->orderBy = '';
        $this->groupBy = '';
        $this->params = [];
        $this->db = 'db';
        $this->table = '';
    }

    /**
     * @param string $db
     * @return $this
     */
    public function db(string $db)
    {
        $this->db = $db;
        return $this;
    }

    /**
     * @param string $table
     * @return $this
     */
    public function table(string $table)
    {
        $this->table = $table;
        return $this;
    }

    /**
     * @param string[]|string $select
     * @return $this
     */
    public function select($select)
    {
        if(is_string($select)){
            $select = explode(',',$select);
        }
        $this->select = $select;
        return $this;
    }

    /**
     * @param array|string|Expression $where
     * @return $this
     */
    public function where($where)
    {
        if(count($this->where) > 0){
            $this->where = [];
        }
        $this->where = is_object($where) ? [$where] : $where;
        return $this;
    }

    /**
     * @param array|string|Expression $where
     * @return $this
     */
    public function andWhere($where)
    {
        if(count($this->where) === 0){
            return $this->where($where);
        }
        $this->where[] = $where;
        return $this;
    }

    /**
     * @param string $order
     * @return $this
     */
    public function orderBy(string $order)
    {
        $this->orderBy = $order;
        return $this;
    }

    /**
     * @param string $by
     * @return $this
     */
    public function groupBy(string $by)
    {
        $this->groupBy = $by;
        return $this;
    }

    /**
     * @param int $n
     * @return $this
     */
    public function limit(int $n)
    {
        $this->limit = $n;
        return $this;
    }

    /**
     * @param int $n
     * @return $this
     */
    public function offset(int $n)
    {
        $this->offset = $n;
        return $this;
    }

    /**
     * @return array
     * @throws Exception
     */
    public function one()
    {
        $this->params = [];
        $sql = $this->createSql() . ' limit 1';
        return Db::queryOne($sql,$this->params,$this->db);
    }

    /**
     * @return array
     * @throws Exception
     */
    public function all()
    {
        $this->params = [];
        $sql = $this->createSql();
        if($this->limit > 0){
            $sql .= ' limit '.$this->offset .','.$this->limit;
        }
        return Db::queryAll($sql,$this->params,$this->db);
    }

    /**
     * @return bool
     * @throws Exception
     */
    public function exists()
    {
        $data = $this->count();
        return $data <= 0 ? false : true;
    }

    /**
     * @param string $field
     * @param string $key
     * @return int
     * @throws Exception
     */
    public function count(string $field = '*',string $key = 'num')
    {
        $lastSelect = $this->select;
        $field = $field == '*' ? '*' : $this->escape($field);
        $this->select("count({$field}) as `{$key}`");
        $data = $this->one();

        //还原select
        $this->select($lastSelect);
        return empty($data[$key]) ? 0 : (int)$data[$key];
    }

    /**
     * @param string $field
     * @return int|float
     * @throws Exception
     */
    public function sum(string $field)
    {
        $lastSelect = $this->select;
        $field = $this->escape($field);
        $this->select("sum({$field}) as t_fast_sum`");
        $data = $this->one();

        //还原select
        $this->select($lastSelect);
        return empty($data['t_fast_sum']) ? 0 : $data['t_fast_sum'];
    }

    /**
     * @param array $insert
     * @return int
     * @throws Exception
     */
    public function insert(array $insert)
    {
        $this->params = [];
        $this->insert = $insert;
        $sql = $this->createInsert();
        return Db::exec($sql,$this->params,$this->db);
    }

    /**
     * @param array $keys
     * @param array $insert
     * @return int
     * @throws Exception
     */
    public function batchInsert(array $keys,array $insert)
    {
        $this->params = [];
        $this->insert = $insert;
        $sql = $this->createBatchInsert($keys);
        return Db::exec($sql,$this->params,$this->db);
    }

    /**
     * @param array $update
     * @param array $condition
     * @return int
     * @throws Exception
     */
    public function update(array $update,array $condition = [])
    {
        $this->params = [];
        $this->update = $update;
        $this->updateWhere = $condition;
        $sql = $this->createUpdate();
        return Db::exec($sql,$this->params,$this->db);
    }

    /**
     * @param array $update
     * @param array $condition
     * @return int
     * @throws Exception
     */
    public function updateCounters(array $update,array $condition = [])
    {
        $this->params = [];
        $this->update = $update;
        $this->updateWhere = $condition;
        $sql = $this->createCounters();
        return Db::exec($sql,$this->params,$this->db);
    }

    /**
     * @param array $condition
     * @return int
     * @throws Exception
     */
    public function delete(array $condition = [])
    {
        $this->params = [];
        $this->deleteWhere = $condition;
        $sql = $this->createDelete();
        return Db::exec($sql,$this->params,$this->db);
    }

    /**
     * @return string
     * @throws Exception
     */
    public function getInsertId()
    {
        return Db::getLastId($this->db);
    }

    /**
     * @return string
     */
    public function createSql()
    {
        $select = $this->createSelect();
        $where = $this->createWhere($this->where);
        $table = $this->table;
        $sql = "select {$select} from {$table}{$where}";
        if(!empty($this->groupBy)){
            $sql .= ' group by '.$this->groupBy;
        }
        if(!empty($this->orderBy)){
            $sql .= ' order by '.$this->orderBy;
        }
        return $sql;
    }

    /**
     * 创建select字段
     * @return string
     */
    public function createSelect()
    {
        if(is_string($this->select)){
            return $this->select;
        }
        if(count($this->select) == 0){
            return '*';
        }
        return implode(',',$this->select);
    }

    /**
     * 创建where SQL
     * @param array $where
     * @return string
     */
    public function createWhere(array $where)
    {
        if(count($where) == 0){
            return '';
        }
        $whereSql = ' where ';
        $arr = [];
        foreach ($where as $key => $item){
            if(is_array($item)){
                if(isset($item[0])){
                    $arr[] = $this->escape($item[1]) . ' ' . $item[0] . ' ?';
                    $this->params[] = $item[2];
                    continue;
                }
                foreach ($item as $field => $val){
                    $arr[] = $this->escape($field) . ' = ?';
                    $this->params[] = $val;
                }
                continue;
            }
            if(is_object($item)){
                $arr[] = $item->sql;
                continue;
            }
            $arr[] = $this->escape($key) . ' = ?';
            $this->params[] = $item;
        }
        return $whereSql . implode(' and ',$arr);
    }

    /**
     * 创建insert SQL
     * @return string
     */
    public function createInsert()
    {
        $keyArr = array_keys($this->insert);
        $this->params = array_values($this->insert);
        $values = [];
        foreach ($keyArr as $k => $v){
            $values[] = '?';
            $keyArr[$k] = $this->escape($v);
        }
        $str = implode(',',$values);
        $keys = implode(',',$keyArr);
        $table = $this->table;
        return "insert into {$table}({$keys}) values({$str})";
    }

    /**
     * 创建批量insert SQL
     * @param array $keyArr
     * @return string
     */
    public function createBatchInsert(array $keyArr)
    {
        $keys = '`' . implode('`,`',$keyArr). '`';
        $table = $this->table;
        $sql = "insert into {$table}({$keys}) values(";

        $fArr = [];
        foreach ($keyArr as $val){
            $fArr[] = '?';
        }
        $vArr = [];
        foreach ($this->insert as $insert) {
            $vArr[] = implode(',',$fArr);
            foreach ($insert as $val){
                $this->params[] = $val;
            }
        }
        $sql .= implode('),(',$vArr). ')';
        return $sql;
    }

    /**
     * 创建更新 SQL
     * @return string
     */
    public function createUpdate()
    {
        $table = $this->table;
        $keys = array_keys($this->update);
        $this->params = array_values($this->update);
        $sql = "update {$table} set ";
        $arr = [];
        foreach ($keys as $key){
            $arr[] = $this->escape($key) . " = ?";
        }
        $sql .= implode(',',$arr) . $this->createWhere($this->updateWhere);
        return $sql;
    }

    /**
     * 创建更新 Counters SQL
     * @return string
     */
    public function createCounters()
    {
        $table = $this->table;
        $sql = "update {$table} set ";
        $arr = [];
        foreach ($this->update as $key => $val){
            $escapeKey = $this->escape($key);
            $f = $val < 0 ? '-' : '+';
            $val = abs($val);
            $arr[] = "{$escapeKey} = {$escapeKey} {$f} {$val}";
        }
        $sql .= implode(',',$arr) . $this->createWhere($this->updateWhere);
        return $sql;
    }

    /**
     * 创建删除 SQL
     * @return string
     */
    public function createDelete()
    {
        $table = $this->table;
        $sql = "delete from {$table}";
        $sql .= $this->createWhere($this->deleteWhere);
        return $sql;
    }

    /**
     * @param string $field
     * @return string
     */
    public function escape(string $field)
    {
        $arr = explode('.',$field);
        foreach ($arr as $key => $val){
            $arr[$key] = "`{$val}`";
        }
        return implode('.',$arr);
    }
}
